package org.opensourceway.sbom.dao;

import org.opensourceway.sbom.model.entity.Vulnerability;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;
import java.util.UUID;

public interface VulnerabilityRepository extends JpaRepository<Vulnerability, UUID> {

    Optional<Vulnerability> findByVulId(String vulId);

    @Query(value = "SELECT * FROM vulnerability WHERE vul_id in :vulIds", nativeQuery = true)
    List<Vulnerability> findByVulIds(List<String> vulIds);

    @Query(value = """
            WITH all_vul AS (
            SELECT v.*, coalesce(
            (SELECT vs.severity FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V3' ORDER BY vs.vector DESC LIMIT 1),
            (SELECT vs.severity FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V2' ORDER BY vs.vector DESC LIMIT 1),
            'UNKNOWN'
            ) severity, coalesce(
            (SELECT vs.score FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V3' ORDER BY vs.vector DESC LIMIT 1),
            (SELECT vs.score FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V2' ORDER BY vs.vector DESC LIMIT 1)
            ) score, coalesce(
            (SELECT vs.scoring_system FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V3' ORDER BY vs.vector DESC LIMIT 1),
            (SELECT vs.scoring_system FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V2' ORDER BY vs.vector DESC LIMIT 1)
            ) scoring_system FROM external_vul_ref evf join vulnerability v on evf.vul_id = v.id
            WHERE (CAST(:packageId AS UUID) IS NULL AND evf.pkg_id IN (
                SELECT id FROM package WHERE sbom_id = (
                    SELECT id FROM sbom WHERE product_id = (
                        SELECT id FROM product WHERE name = :productName))))
            OR (CAST(:packageId AS UUID) IS NOT NULL AND evf.pkg_id = CAST(:packageId AS UUID))
            )
            SELECT DISTINCT(id), * FROM all_vul v WHERE (:severity IS NULL OR v.severity = :severity)
            AND (:vulId IS NULL OR v.vul_id = :vulId)
            ORDER BY v.score DESC NULLS LAST, v.vul_id DESC, v.scoring_system DESC NULLS LAST
            """,
            countQuery = """
                    WITH all_vul AS (
                    SELECT v.*, coalesce(
                    (SELECT vs.severity FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V3' ORDER BY vs.vector DESC LIMIT 1),
                    (SELECT vs.severity FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V2' ORDER BY vs.vector DESC LIMIT 1),
                    'UNKNOWN'
                    ) severity, coalesce(
                    (SELECT vs.score FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V3' ORDER BY vs.vector DESC LIMIT 1),
                    (SELECT vs.score FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V2' ORDER BY vs.vector DESC LIMIT 1)
                    ) score, coalesce(
                    (SELECT vs.scoring_system FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V3' ORDER BY vs.vector DESC LIMIT 1),
                    (SELECT vs.scoring_system FROM vul_score vs WHERE vs.vul_id = v.id AND vs.scoring_system = 'CVSS_V2' ORDER BY vs.vector DESC LIMIT 1)
                    ) scoring_system  FROM external_vul_ref evf join vulnerability v on evf.vul_id = v.id
                    WHERE (CAST(:packageId AS UUID) IS NULL AND evf.pkg_id IN (
                        SELECT id FROM package WHERE sbom_id = (
                            SELECT id FROM sbom WHERE product_id = (
                                SELECT id FROM product WHERE name = :productName))))
                    OR (CAST(:packageId AS UUID) IS NOT NULL AND evf.pkg_id = CAST(:packageId AS UUID))
                    )
                    SELECT COUNT(DISTINCT(id)) FROM all_vul v WHERE (:severity IS NULL OR v.severity = :severity)
                    AND (:vulId IS NULL OR v.vul_id = :vulId)
                    """,
            nativeQuery = true)
    Page<Vulnerability> findByProductNameAndPackageIdAndSeverityAndVulId(@Param("productName") String productName,
                                                                         @Param("packageId") UUID packageId,
                                                                         @Param("severity") String severity,
                                                                         @Param("vulId") String vulId,
                                                                         Pageable pageable);

}